package com.yunji.generate.service;


import com.yunji.generate.bean.ColumnInfo;
import com.yunji.generate.bean.TableInfo;
import com.yunji.generate.common.DatabaseConfig;
import com.yunji.generate.common.ResultMessage;
import com.yunji.generate.exception.DatabaseException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;

/**
 *
 **/
public class DatabaseHelper {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseHelper.class);

    private static Connection getConnection(DatabaseConfig config) {
        try {
            Properties prop = new Properties();
            prop.setProperty("user", config.getUsername());
            prop.setProperty("password", config.getPassword());
            // 获取表备注, mysql
            prop.setProperty("useInformationSchema", "true");
            // 获取表备注, oracle
            prop.put("remarksReporting", "true");

            return DriverManager.getConnection(config.getUrl(), prop);
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    /**
     * 异步获取数据表详细信息
     */
    public static void asyncTables(DatabaseConfig config, List<TableInfo> tables, IProcessCallback<TableInfo> callback) {
        if (callback == null) {
            return;
        }
        Thread t = new Thread(() -> {
            callback.onProcessBegin(new IProcessCallback.ListParameter<>(tables));
            int sequence = 0;
            try (Connection conn = getConnection(config)) {
                for (TableInfo table : tables) {
                    table.setColumns(getColumnInfo(conn, table.getName().toUpperCase()));
                    callback.onProcessing(new IProcessCallback.Parameter<>(table, ++sequence, ResultMessage.success()));
                }
            } catch (SQLException e) {
                logger.error("异步获取表信息失败", e);
            }
            callback.onProcessEnd(new IProcessCallback.ListParameter<>(tables));
        });
        t.setName("Async Table Thread");
        t.start();
        try {
            t.join();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 同步获取数据表详细信息
     */
    public static List<TableInfo> syncTables(DatabaseConfig config, List<TableInfo> tables) {
        try (Connection conn = getConnection(config)) {
            for (TableInfo table : tables) {
                table.setColumns(getColumnInfo(conn, table.getName().toUpperCase()));
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
        return tables;
    }

    /**
     * 获取表的列信息
     */
    private static List<ColumnInfo> getColumnInfo(Connection conn, String tableName) throws SQLException {
        String primaryKey = getPrimaryKey(conn, tableName);
        String sql = String.format("select * from %s where 1 = 2", tableName);

        List<ColumnInfo> columns = getColumnBaseInfo(conn, tableName);
        Map<String, ColumnInfo> map = columns.stream().collect(Collectors.toMap(ColumnInfo::getColumnName, e -> e));

        try (PreparedStatement stmt = conn.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {
            ResultSetMetaData data = rs.getMetaData();

            for (int i = 1; i <= data.getColumnCount(); i++) {
                ColumnInfo columnInfo = map.get(data.getColumnName(i));
                columnInfo.setPrimary(primaryKey.equals(columnInfo.getColumnName()));
                String columnClassName = data.getColumnClassName(i);
                columnInfo.setColumnClassName(columnClassName);
            }
        }
        return columns;
    }

    /**
     * 获取表的基本列信息
     */
    private static List<ColumnInfo> getColumnBaseInfo(Connection conn, String tableName) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getColumns(conn.getCatalog(), null, tableName, null)) {
            //这里只是拿备注
            List<ColumnInfo> columns = new ArrayList<>();
            while (rs.next()) {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
                columnInfo.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
                columnInfo.setDataType(rs.getInt("DATA_TYPE"));
                columnInfo.setTypeName(rs.getString("TYPE_NAME"));
                columnInfo.setColumnName(rs.getString("COLUMN_NAME"));
                columnInfo.setRemarks(rs.getString("REMARKS"));
                columns.add(columnInfo);
            }
            return columns;
        }
    }

    /**
     * 获取表的主键
     */
    private static String getPrimaryKey(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet resultSet = metaData.getPrimaryKeys(null, null, tableName);
        return resultSet.next() ? resultSet.getString("COLUMN_NAME") : "";
    }

    /**
     * 获取所有数据表的基础信息，包括表名及注释
     */
    public static List<TableInfo> getTableSimpleInfo(DatabaseConfig config) {
        List<TableInfo> tables = new ArrayList<>();

        try (Connection conn = getConnection(config)) {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tables.add(new TableInfo(rs.getString("TABLE_NAME"), rs.getString("REMARKS")));
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
        return tables;
    }

    public static void main(String[] args) {
        DatabaseConfig config = new DatabaseConfig();
        config.setUsername("rpa");
        config.setPassword("rpa");
        config.setUrl("jdbc:oracle:thin:@192.168.1.213:1521:YF03");

        try (Connection conn = getConnection(config)) {
            List<ColumnInfo> list = getColumnInfo(conn, "RH_TEMPLATE");
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}